/* ============================================================ */
/*   Database name:  CCPM                                    */
/*   DBMS name:      Microsoft SQL Server 6.x                   */
/*   Created on:     7/28/2009  8:37 PM                         */
/* ============================================================ */


/* ============================================================ */
/*   Table: ENT_1                                               */
/* ============================================================ */
create table GIAOVIEN
(
    MAGV          char(10)              not null,
    TENGV         nvarchar(50)          null    ,
    constraint PK_ENT_1 primary key (MAGV)
)
go

/* ============================================================ */
/*   Table: LOAIPHONG                                           */
/* ============================================================ */
create table LOAIPHONG
(
    MALOAIPHONG   char(10)              not null,
    TENLOAIPHONG  nvarchar(50)              null    
    constraint PK_LOAIPHONG primary key (MALOAIPHONG)
)
go

/* ============================================================ */
/*   Table: THU                                                 */
/* ============================================================ */
create table THU
(
    MATHU         int                   not null,
    TENTHU        nvarchar(10)              null    ,
    constraint PK_THU primary key (MATHU)
)
go

/* ============================================================ */
/*   Table: TUAN                                                */
/* ============================================================ */
create table TUAN
(
    MATUAN        int                   not null,
    NGAYBD        datetime              null    ,
    NGAYKT        datetime              null    ,
    constraint PK_TUAN primary key (MATUAN)
)
go

/* ============================================================ */
/*   Table: TIET                                                */
/* ============================================================ */
create table TIET
(
    MATIET        int                   not null,
    GIOBD         char(10)              null    ,
    GIOKT         char(10)              null    ,
    constraint PK_TIET primary key (MATIET)
)
go

/* ============================================================ */
/*   Table: PHONG                                               */
/* ============================================================ */
create table PHONG
(
    MAPHONG       char(10)              not null,
    MALOAIPHONG   char(10)              null    ,
    SUCCHUA       int                   null    ,
    constraint PK_PHONG primary key (MAPHONG)
)
go

/* ============================================================ */
/*   Index: RELATION_47_FK                                      */
/* ============================================================ */
create index RELATION_47_FK on PHONG (MALOAIPHONG)
go

/* ============================================================ */
/*   Table: ENT_41                                              */
/* ============================================================ */
create table DANGKY
(
    MAPHONG       char(10)              not null,
    MATIET        int                   not null,
    MATHU         int                   not null,
    MATUAN        int                   not null,
    MAGV          char(10)              not null,
    constraint PK_ENT_41 primary key (MAPHONG, MATIET, MATHU, MATUAN, MAGV)
)
go

/* ============================================================ */
/*   Index: RELATION_43_FK                                      */
/* ============================================================ */
create index RELATION_43_FK on DANGKY (MAGV)
go

/* ============================================================ */
/*   Index: RELATION_44_FK                                      */
/* ============================================================ */
create index RELATION_44_FK on DANGKY (MATUAN)
go

/* ============================================================ */
/*   Index: RELATION_45_FK                                      */
/* ============================================================ */
create index RELATION_45_FK on DANGKY (MATHU)
go

/* ============================================================ */
/*   Index: RELATION_48_FK                                      */
/* ============================================================ */
create index RELATION_48_FK on DANGKY (MATIET)
go

/* ============================================================ */
/*   Index: RELATION_49_FK                                      */
/* ============================================================ */
create index RELATION_49_FK on DANGKY (MAPHONG)
go

alter table PHONG
    add constraint FK_PHONG_RELATION__LOAIPHON foreign key  (MALOAIPHONG)
       references LOAIPHONG (MALOAIPHONG)
go

alter table DANGKY
    add constraint FK_ENT_41_RELATION__ENT_1 foreign key  (MAGV)
       references GIAOVIEN (MAGV)
go

alter table DANGKY
    add constraint FK_ENT_41_RELATION__TUAN foreign key  (MATUAN)
       references TUAN (MATUAN)
go

alter table DANGKY
    add constraint FK_ENT_41_RELATION__THU foreign key  (MATHU)
       references THU (MATHU)
go

alter table DANGKY
    add constraint FK_ENT_41_RELATION__TIET foreign key  (MATIET)
       references TIET (MATIET)
go

alter table DANGKY
    add constraint FK_ENT_41_RELATION__PHONG foreign key  (MAPHONG)
       references PHONG (MAPHONG)
go

----------------------------------------------------------------------------

if exists(select name from sysobjects where name like 'sp_Auto_ID_GiaoVien') 
	drop proc sp_Auto_ID_GiaoVien
go

create proc sp_Auto_ID_GiaoVien
	@ID char(10) output
as
begin	
	declare @Flag bit
	set @Flag = 1
	set @ID = 1
	while (@Flag = 1)
	begin
		if exists (select 'True' from GiaoVien where MaGV = 'GV' + Convert(char(10),@ID))
		begin
			set @ID = @ID + 1
		end
		else
		begin
			set @ID = 'GV' + convert(char(10), @ID)
			set @Flag = 0
		end
	end
end
go

if exists(select name from sysobjects where name like 'sp_Insert_GiaoVien') 
	drop proc sp_Insert_GiaoVien
go

create proc sp_Insert_GiaoVien
	@TenGV nvarchar(50)
as
begin		
		declare @MaGV char(10)		
		exec sp_Auto_ID_GiaoVien @MaGV output				
		insert into GIAOVIEN values(@MaGV, @TenGV)
end
go

exec sp_Insert_GiaoVien 'Tang Thanh Ha'
go
exec sp_Insert_GiaoVien 'Ho Ngoc Ha'
go
exec sp_Insert_GiaoVien 'Ly Hai'
go
exec sp_Insert_GiaoVien 'Ho Quynh Huong'
go
exec sp_Insert_GiaoVien 'Ngoc Son'
go
exec sp_Insert_GiaoVien 'Ngoc Hai'
go
exec sp_Insert_GiaoVien 'Kim Khach'
go
exec sp_Insert_GiaoVien 'Ngoc Khach'
go
exec sp_Insert_GiaoVien 'Ung Hoang Phuc'
go
exec sp_Insert_GiaoVien 'Dam Vinh Hung'
go

if exists(select name from sysobjects where name like 'sp_Auto_ID_LoaiPhong') 
	drop proc sp_Auto_ID_LoaiPhong
go

create proc sp_Auto_ID_LoaiPhong
	@ID char(10) output
as
begin	
	declare @Flag bit
	set @Flag = 1
	set @ID = 1
	while (@Flag = 1)
	begin
		if exists (select 'True' from LoaiPhong where MaLoaiPhong = 'LP' + Convert(char(10),@ID))
		begin
			set @ID = @ID + 1
		end
		else
		begin
			set @ID = 'LP' + convert(char(10), @ID)
			set @Flag = 0
		end
	end
end
go

if exists(select name from sysobjects where name like 'sp_Insert_LoaiPhong') 
	drop proc sp_Insert_LoaiPhong
go

create proc sp_Insert_LoaiPhong
	@TenLoaiPhong char(10)
as
begin tran
	if exists(select* from LOAIPHONG where TENLOAIPHONG=@TenLoaiPhong)
		rollback tran 
	else
	begin
		declare @MaLoaiPhong char(10)		
		exec sp_Auto_ID_LoaiPhong @MaLoaiPhong output		
		insert into LOAIPHONG values(@MaLoaiPhong, @TenLoaiPhong)
		commit tran
	end
return
go

exec sp_Insert_LoaiPhong 'Phong hoc'
go
exec sp_Insert_LoaiPhong 'Phong hop'
go
exec sp_Insert_LoaiPhong 'Phong thuc hanh'
go

if exists(select name from sysobjects where name like 'sp_Auto_ID_Phong') 
	drop proc sp_Auto_ID_Phong
go

create proc sp_Auto_ID_Phong
	@ID char(10) output--@ID int output
as
begin	
	declare @Flag bit
	set @Flag = 1
	set @ID = 1
	while (@Flag = 1)
	begin
		if exists (select 'True' from Phong where maphong = 'P' + Convert(char(10),@ID))
		begin
			set @ID = @ID + 1
		end
		else
		begin
			set @ID = 'P' + convert(char(10), @ID)
			set @Flag = 0
		end
	end
end
go

if exists(select name from sysobjects where name like 'sp_Insert_Phong') 
	drop proc sp_Insert_Phong
go

create proc sp_Insert_Phong
	@MaLoaiPhong char(10),
	@SucChua int
as
begin tran		
	declare @MaPhong char(10)
	declare @ID char(10)		
	exec sp_Auto_ID_Phong @ID output		
	set @MaPhong = @ID	
	if exists(select* from PHONG where MAPHONG=@MaPhong and MALOAIPHONG=@MaLoaiPhong)
		rollback tran
	else
	begin
		insert into PHONG values(@MaPhong, @MaLoaiPhong, @SucChua)
		commit tran	
	end	
return
go

exec sp_Insert_Phong 'LP1', 50
go
exec sp_Insert_Phong 'LP1', 50
go
exec sp_Insert_Phong 'LP1', 50
go
exec sp_Insert_Phong 'LP1', 50
go
exec sp_Insert_Phong 'LP1', 100
go
exec sp_Insert_Phong 'LP1', 100
go
exec sp_Insert_Phong 'LP1', 200
go
exec sp_Insert_Phong 'LP1', 150
go
exec sp_Insert_Phong 'LP1', 200
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP3', 50
go
exec sp_Insert_Phong 'LP2', 200
go
exec sp_Insert_Phong 'LP2', 300
go
exec sp_Insert_Phong 'LP2', 300
go
exec sp_Insert_Phong 'LP2', 300
go
exec sp_Insert_Phong 'LP2', 200
go

if exists(select name from sysobjects where name like 'sp_Auto_ID_Thu') 
	drop proc sp_Auto_ID_Thu
go

create proc sp_Auto_ID_Thu
	@ID int output
as
begin
	declare @MaThu_cur int, @MaThu int
	set @MaThu = 0
	set @ID = 0
	declare @cur cursor 
	set @cur = cursor for select MaThu from THU
	open @cur
	fetch next from @cur into @MaThu_cur
	while @@fetch_status = 0
	begin
		if(@MaThu_cur - @MaThu <= 1)
			set @ID = @MaThu + 1			
		else
			break
		set @MaThu  = @MaThu_cur					
		fetch next from @cur into @MaThu_cur
	end
	set @ID = @ID + 1 
	close @cur
	deallocate @cur	
end
go

if exists(select name from sysobjects where name like 'sp_Insert_Thu') 
	drop proc sp_Insert_Thu
go

create proc sp_Insert_Thu
	@TenThu char(10)
as
begin tran 
	if exists(select* from THU where TENTHU=@TenThu)
		rollback tran
	else
	begin
		declare @MaThu int
		exec sp_Auto_ID_Thu @MaThu output
		insert into THU values(@MaThu, @TenThu)
		commit tran
	end
return
go

exec sp_Insert_Thu 'Thu Chu nhat'
go
exec sp_Insert_Thu 'Thu 2'
go
exec sp_Insert_Thu 'Thu 3'
go
exec sp_Insert_Thu 'Thu 4'
go
exec sp_Insert_Thu 'Thu 5'
go
exec sp_Insert_Thu 'Thu 6'
go
exec sp_Insert_Thu 'Thu 7'
go

if exists(select name from sysobjects where name like 'sp_Auto_ID_Tiet') 
	drop proc sp_Auto_ID_Tiet
go

create proc sp_Auto_ID_Tiet
	@ID int output
as
begin
	declare @MaTiet_cur int, @MaTiet int
	set @MaTiet = 0
	set @ID = 0
	declare @cur cursor 
	set @cur = cursor for select MaTiet from TIET
	open @cur
	fetch next from @cur into @MaTiet_cur
	while @@fetch_status = 0
	begin
		if(@MaTiet_cur - @MaTiet <= 1)
			set @ID = @MaTiet + 1			
		else
			break
		set @MaTiet  = @MaTiet_cur					
		fetch next from @cur into @MaTiet_cur
	end
	set @ID = @ID + 1 
	close @cur
	deallocate @cur	
end
go

if exists(select name from sysobjects where name like 'sp_Insert_Tiet') 
	drop proc sp_Insert_Tiet
go		

create proc sp_Insert_Tiet
	@GioBD char(10),
	@GioKT char(10)
as
begin
	declare @MaTiet int
	exec sp_Auto_ID_Tiet @MaTiet output
	insert into TIET values(@MaTiet, @GioBD, @GioKT)
end
go

exec sp_Insert_Tiet '7h30', '8h15'
go
exec sp_Insert_Tiet '8h15', '9h'
go
exec sp_Insert_Tiet '9h', '9h45'
go
exec sp_Insert_Tiet '9h45', '10h30'
go
exec sp_Insert_Tiet '10h30', '11h15'
go
exec sp_Insert_Tiet '11h15', '12h'
go
exec sp_Insert_Tiet '12h30', '13h15'
go
exec sp_Insert_Tiet '13h15', '14h'
go
exec sp_Insert_Tiet '14h', '14h45'
go
exec sp_Insert_Tiet '14h45', '15h30'
go
exec sp_Insert_Tiet '15h30', '16h15'
go
exec sp_Insert_Tiet '16h15', '17h'
go
exec sp_Insert_Tiet '17h', '17h45'
go
exec sp_Insert_Tiet '17h45', '18h30'
go
exec sp_Insert_Tiet '18h30', '19h15'
go
exec sp_Insert_Tiet '19h15', '20h30'
go

if exists(select name from sysobjects where name like 'sp_Auto_ID_Tuan') 
	drop proc sp_Auto_ID_Tuan
go

create proc sp_Auto_ID_Tuan
	@ID int output
as
begin
	declare @MaTuan_cur int, @MaTuan int
	set @MaTuan = 0
	set @ID = 0
	declare @cur cursor 
	set @cur = cursor for select MaTuan from TUAN
	open @cur
	fetch next from @cur into @MaTuan_cur
	while @@fetch_status = 0
	begin
		if(@MaTuan_cur - @MaTuan <= 1)
			set @ID = @MaTuan + 1			
		else
			break
		set @MaTuan  = @MaTuan_cur					
		fetch next from @cur into @MaTuan_cur
	end
	set @ID = @ID + 1 
	close @cur
	deallocate @cur	
end
go

if exists(select name from sysobjects where name like 'sp_Insert_Tuan') 
	drop proc sp_Insert_Tiet
go		

create proc sp_Insert_Tuan
	@NgayBD datetime,
	@NgayKT datetime
as
begin  
	declare @MaTuan int
	exec sp_Auto_ID_Tuan @MaTuan output
	insert into TUAN values(@MaTuan, @NgayBD, @NgayKT)
end
go

exec sp_Insert_Tuan '6/28/2009', '7/4/2009'
go
exec sp_Insert_Tuan '7/7/2009', '7/11/2009'
go
exec sp_Insert_Tuan '7/12/2009', '7/18/2009'
go
exec sp_Insert_Tuan '7/19/2009', '7/25/2009'
go
exec sp_Insert_Tuan '7/26/2009', '8/1/2009'
go
exec sp_Insert_Tuan '8/2/2009', '8/8/2009'
go
exec sp_Insert_Tuan '8/9/2009', '8/15/2009'
go
exec sp_Insert_Tuan '8/16/2009', '8/22/2009'
go
exec sp_Insert_Tuan '8/21/2009', '8/29/2009'
go

create proc sp_Insert_DangKy
	@MaPhong char(10),
	@MaTiet int,
	@MaThu int, 
	@MaTuan int,
	@MaGV char(10)
as
	insert into DANGKY values(@MaPhong, @MaTiet, @MaThu, @MaTuan, @MaGV)
go

